package study.top.mydb;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import org.apache.log4j.Logger;

import study.top.common.DBHandler;
import study.top.common.DateTime;
import study.top.jsp.PageHandler;
import study.top.mydb.items.Category1Item;
import study.top.mydb.items.PwQuestionItem;




/**
 * 
 * @author SEUNG-Won��
 * 
 */
public class PwQuestion {

	static Logger logger = Logger.getLogger(PwQuestion.class);
	
	


	// �����ͺ��̽� ���� ��ü�� ���� ����
	DBHandler db = null;
		
	public PwQuestion() {
		// DB��ü�� ���� ���� ����
		db = DBHandler.getInstance();
	}
	private static final String TABLE = "PwQuestion";

	
	
	
	/**
	 * PW �� �߰��޼��墾
	 * 
	 * @param pw_question
	 *            PW ��
	 * @return �߰� ����
	 */
	public int insertPwQuestion(String pw_question) {

		logger.debug("=========method start >> insertPwQuestion");
		logger.debug("[PARAMS] pw_question = " + pw_question);

		// ������ �� ����
		int seq = -1;

		// SQL ������ ���
		pw_question = pw_question.replace("'", "\'");

		// reg_date, edit_date�� ���ؼ� ����
		String date = DateTime.getInstance().getNowDateTimeString();

		// ���̺��� �ڵ� ������ �ƴϹǷ�,
		// �ִ밪 + 1 ������� �޼��� �ȿ��� ���Ѵ�.

		seq = db.getMax(TABLE, "seq", null) + 1;

		logger.debug("추가생성된 번호 : " + seq);

		// Ʈ������
		db.beginTrans();

		// INSERT ó���� ���� ����
		String sql = "INSERT INTO pw_question("
				+ " pw_question, reg_date, edit_date"
				+ ") VALUES ("+" '%s','%s', '%s')";

		boolean result = db.execSql(sql, pw_question, date, date);

		// -----------------------------------------
		// ���ó��
		// -----------------------------------------
		if (result) {
			db.commit();
			logger.debug("추가성공 >> " + seq + "번 추가");
		} else {
			db.rollback();
			logger.error("추가실패 >> 추가실패");
		}

		logger.debug("=============== method end >> insertPwQuestion");

		return seq;
	}

	
	
	
	
	/**
	 * �� �����ϴ� �޼���
	 * 
	 * @param pwseq
	 *            ���Ϸù�ȣ
	 * @param old_pw_question
	 *            �ʱ⼳����
	 * @param pw_question
	 *            ��������
	 * @return ��������
	 */
	public boolean updatePwQuestion(int seq, String pw_question) {

		logger.debug("=========method start >> updatePwQuestion");
		logger.debug("[PARAMS] pw_question = " + pw_question);

		// SQL ������ ���

		pw_question = pw_question.replace("'", "\'");

		// ������ �� ����
		boolean result = false;


		
		if (db.getCount("Pw_Qeustion", "seq", "seq=" + seq) > 0){
			
			logger.error("���� ���Ἲ ����");
			return result;
			
			
		}
		
	
		// Ʈ������
		db.beginTrans();

		// UPDATE ó���� ���� ����
		String sql = "UPDATE pw_question SET pw_question = '%s', edit_date='%s' WHERE seq = %d;";

		String date = DateTime.getInstance().getNowDateTimeString();

		result = db
				.execSql(sql,pw_question, date, seq);

		// -----------------------------------------
		// ���ó��
		// -----------------------------------------
		if (result) {
			db.commit();
			logger.debug("ó����� >> " + seq + "�� �� ��������");
		} else {
			db.rollback();
			logger.error("ó����� >> �� ���� ���� ");
		}

		logger.debug("=============== method end >> updatePwQuestion");

		return result;
	}

	
	
	
	
	/**
	 * ������ϴ� �޼���
	 * 
	 * 
	 * @param pwseq		���Ϸù�ȣ
	 * @return 			���� ����
	 */
	public boolean deletePwQuestion(int seq) {

		logger.debug("=========method start >> deletePwQuestion");
		logger.debug("[PARAMS] pwseq = " + seq);

		boolean result = false;

		// �� �����͸� �����ϴ� �ٸ� �����͵��� ���� ���� �˻�
		if (db.getCount("Pw_Qeustion", "seq", "seq=" + seq) > 0) {

			logger.error("���� ���Ἲ ����");
			return result;
		}

		// Ʈ������
		db.beginTrans();

		// DELETE ó���� ���� ����
		String sql = "DELETE FROM pw_question WHERE seq = %d";
		result = db.execSql(sql, seq);

		// -----------------------------------------
		// ���ó��
		// -----------------------------------------
		if (result) {
			db.commit();
			logger.debug("삭제성공 >> " + seq + "번 삭제되었습니다");
		} else {
			db.rollback();
			logger.error("삭제실패 >> 삭제실패 ");
		}

		logger.debug("=============== method end >> deletePwQuestion");

		return result;
	}

	
	
	
	
	
	
	
	
	
	/**
	 * ī�װ?1 �� �Ѱ��� �����ִ� �޼���
	 * @param seq ������ ī�װ?1 seq
	 * @return Category1Item ���� �� ī�װ?1 ���� ����
	 */
	public PwQuestionItem selectpwquestion(int seq){
		logger.debug("=========method start >> selectpwquestion");
		logger.debug("[PARAMS] seq = " + seq);
		PwQuestionItem item = null;
		
		String sql = "SELECT seq, pw_question, reg_date,edit_date FROM pw_question WHERE seq='%d';";
		ResultSet rs = db.getResult(sql, seq);
		logger.debug("[rs] rs = " + rs);
		if(rs != null) {
			// ������ ��ȸ ��� �ִ� ���
			try {
				rs.next();
				
				// 1) ResultSet�� ��� �����͸� ������ ����
				int pwno = rs.getInt(1);
				String pw_Qeustion = rs.getString(2);
				String reg_date = rs.getString(3);
				String edit_date = rs.getString(4);
				
				logger.debug("pwno : " + pwno);
				logger.debug("pw_Qeustion : " + pw_Qeustion);
				logger.debug("reg_date : " + reg_date);
				logger.debug("edit_date : " + edit_date);
				
				// 2) ����� ���� ��ü�� ����
				item = new PwQuestionItem(seq, pw_Qeustion, reg_date, edit_date);
				
			} catch (SQLException e) {
				logger.error(e);
			} finally {
				// 3) ������ ��ȸ�� ���� �޸� �ݳ�
				try {
					rs.close();
				} catch (SQLException e) {}
				rs = null;
			}
		}
		
		if(item == null) {
			logger.error("������ ��ȸ ����");
		} else {
			logger.debug("������ ��ȸ ����");
			logger.debug(item.toString());
		}
		
		logger.debug("=========method end >> selectpwquestion");
		return item;
	}
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	/**
	 * 
	 * �� ��ü���� �޼���
	 * 
	 * @return		ArrayList<PwQuestionItem> 
	 */
	public ArrayList<PwQuestionItem> selectQeustionList() {

		logger.debug("=========method end >> selectQeustionList");
		ArrayList<PwQuestionItem> list = null;

		// ������ ��ȸ
		String sql = "SELECT seq, pw_question, reg_date,edit_date FROM pw_question";

		ResultSet rs = db.getResult(sql);

		if (rs != null) {
			// ������ ��ȸ ��� �ִ� ���
			try {
				list = new ArrayList<PwQuestionItem>();

				while (rs.next()) {

					// 1) ResultSet�� ��� �����͸� ������ ����
					int seq = rs.getInt(1);
					String Pw_Qeustion = rs.getString(2);
					String reg_date = rs.getString(3);
					String edit_date = rs.getString(4);

					// 2) ����� ���� ��ü�� ����
					PwQuestionItem item = new PwQuestionItem(seq, Pw_Qeustion,
							reg_date, edit_date);

					// 3) ArrayList�� �߰�
					list.add(item);
				}
			} catch (SQLException e) {
				logger.error(e);
			} finally {
				// 4) ������ ��ȸ�� ���� �޸� �ݳ�
				try {
					rs.close();
				} catch (SQLException e) {
				}
				rs = null;
			}
		}

		if (list == null) {
			logger.error("������ ��ȸ ����");
		} else {
			logger.debug("������ ��ȸ ���� >> " + list.size() + "�� ��ȸ��");
		}

		logger.debug("=============== method end >> deletePwQuestion");

		return list;
	}
	
	
	
	
	
	
	/**
	 * category1,2 List, Pagging method
	 * @param page // now page number
	 * @param showPage // show in a list page count
	 * @param pageNumber // under the page number count
	 * @return
	 */
	public ArrayList<PwQuestionItem> pwquestionlist(int page, int showPage, int pageNumber){
		logger.debug("=========method start >> pwquestionlist");
		ArrayList<PwQuestionItem> list = null;
		
		String count_table = "(";
		count_table += "select seq ";
		count_table += " from pw_question ";
		//count_table += " where ca1.seq = '1' ";
		//count_table += " group by ca1.seq ";
		count_table += ") AS tbl";

		//전체 글 갯수 조회
		int count = db.getCount(count_table, "*", null);
		
		if(count < 1){
			return null;
		}
		
		// 페이징 처리 --> 한 페이지에 8개씩, 페이지번호는 5개 출력
		PageHandler p = PageHandler.getInstance(page, count, showPage, pageNumber);
		
		// 글 목록 조회
		String sql = "SELECT ";
		sql += "seq, pw_question, reg_date, edit_date ";
		sql += "from pw_question ";
		sql += "LIMIT %d, %d";
		
		ResultSet rs = db.getResult(sql, p.getStartRec(), p.getPageArticleCount());
		
		if(rs == null){
			return null;
		}
		
		list = new ArrayList<PwQuestionItem>();
		
		try{
			while(rs.next()){
				int pwno = rs.getInt(1);
				String pwquestion = rs.getString(2);
				String reg_date = rs.getString(3);
				String edit_date = rs.getString(4);

				PwQuestionItem item = new PwQuestionItem(pwno, pwquestion, reg_date, edit_date);

				list.add(item);
			}
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			try{
				rs.close();
			}catch(SQLException e){
				e.printStackTrace();
			}
			rs = null;
		}
		
		if (list == null) {
			logger.error("List is NULL");
		} else {
			logger.debug("List Size is >> " + list.size() + "...");
		}
		
		logger.debug("=========method end >> pwquestionlist");
		return list;
	}
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	

}
